package com.imut.lagain.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.imut.lagain.entity.MemoryEntry;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 璁板繂鏉＄洰鏁版嵁璁块棶灞?
 */
@Mapper
public interface MemoryEntryRepository extends BaseMapper<MemoryEntry> {
    
    /**
     * 鏍规嵁鑳跺泭ID鏌ヨ鏉＄洰鍒楄〃
     * @param capsuleId 鑳跺泭ID
     * @return 鏉＄洰鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE capsule_id = #{capsuleId} AND is_deleted = 0 ORDER BY sort_order ASC, create_time ASC")
    List<MemoryEntry> findByCapsuleId(@Param("capsuleId") Long capsuleId);
    
    /**
     * 鏍规嵁鑳跺泭ID鍒嗛〉鏌ヨ鏉＄洰鍒楄〃
     * @param capsuleId 鑳跺泭ID
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE capsule_id = #{capsuleId} AND is_deleted = 0 ORDER BY sort_order ASC, create_time ASC")
    IPage<MemoryEntry> findByCapsuleIdWithPage(@Param("capsuleId") Long capsuleId, Page<MemoryEntry> page);
    
    /**
     * 鏍规嵁鏉＄洰绫诲瀷鏌ヨ鏉＄洰鍒楄〃
     * @param type 鏉＄洰绫诲瀷
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE content_type = #{type} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByType(@Param("type") Integer type, Page<MemoryEntry> page);
    
    /**
     * 鏍规嵁鏉＄洰绫诲瀷鏌ヨ鏉＄洰鍒楄〃
     * @param entryType 鏉＄洰绫诲瀷
     * @return 鏉＄洰鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE content_type = #{entryType} AND is_deleted = 0 ORDER BY create_time DESC")
    List<MemoryEntry> findByEntryType(@Param("entryType") String entryType);
    
    /**
     * 鏍规嵁鏉＄洰绫诲瀷鍒嗛〉鏌ヨ鏉＄洰鍒楄〃
     * @param entryType 鏉＄洰绫诲瀷
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE content_type = #{entryType} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByEntryType(@Param("entryType") String entryType, Page<MemoryEntry> page);
    
    /**
     * 鏍规嵁鏉＄洰绫诲瀷鍒嗛〉鏌ヨ鏉＄洰鍒楄〃
     * @param entryType 鏉＄洰绫诲瀷
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE content_type = #{entryType} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByEntryTypeWithPage(@Param("entryType") String entryType, Page<MemoryEntry> page);
    
    /**
     * 鏍规嵁鑳跺泭ID鍜屾潯鐩被鍨嬫煡璇㈡潯鐩垪琛?
     * @param capsuleId 鑳跺泭ID
     * @param entryType 鏉＄洰绫诲瀷
     * @return 鏉＄洰鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE capsule_id = #{capsuleId} AND content_type = #{entryType} AND is_deleted = 0 ORDER BY create_time DESC")
    List<MemoryEntry> findByCapsuleIdAndEntryType(@Param("capsuleId") Long capsuleId, @Param("entryType") String entryType);
    
    /**
     * 鏍规嵁鑳跺泭ID鍜屾潯鐩被鍨嬪垎椤垫煡璇㈡潯鐩垪琛?
     * @param capsuleId 鑳跺泭ID
     * @param entryType 鏉＄洰绫诲瀷
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE capsule_id = #{capsuleId} AND content_type = #{entryType} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByCapsuleIdAndType(@Param("capsuleId") Long capsuleId, @Param("entryType") String entryType, Page<MemoryEntry> page);

    /**
     * 鏍规嵁鏂囦欢绫诲瀷鏌ヨ鏉＄洰
     * @param fileType 鏂囦欢绫诲瀷
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE content_type = #{fileType} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByFileType(@Param("fileType") String fileType, Page<MemoryEntry> page);

    /**
     * 鏍规嵁鏂囦欢URL鏌ヨ鏉＄洰
     * @param fileUrl 鏂囦欢URL
     * @return 鏉＄洰淇℃伅
     */
    @Select("SELECT * FROM capsule_entry WHERE voice_url = #{fileUrl} AND is_deleted = 0")
    MemoryEntry findByFileUrl(@Param("fileUrl") String fileUrl);

    /**
     * 鏌ヨ鎸囧畾鏃堕棿鑼冨洿鍐呭垱寤虹殑鏉＄洰
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @return 鏉＄洰鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE create_time BETWEEN #{startTime} AND #{endTime} AND is_deleted = 0 ORDER BY create_time DESC")
    List<MemoryEntry> findByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 鏌ヨ鎸囧畾鏃堕棿鑼冨洿鍐呭垱寤虹殑鏉＄洰锛堝垎椤碉級
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE create_time BETWEEN #{startTime} AND #{endTime} AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByCreateTimeBetween(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, Page<MemoryEntry> page);

    /**
     * 鏍规嵁鐢ㄦ埛ID鍜屾椂闂磋寖鍥存煡璇㈡潯鐩紙鍒嗛〉锛?
     * @param userId 鐢ㄦ埛ID
     * @param startTime 寮€濮嬫椂闂?
     * @param endTime 缁撴潫鏃堕棿
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT me.* FROM capsule_entry me " +
            "INNER JOIN memory_capsule mc ON me.capsule_id = mc.id " +
            "WHERE mc.user_id = #{userId} AND me.create_time BETWEEN #{startTime} AND #{endTime} " +
            "AND me.is_deleted = 0 AND mc.is_deleted = 0 ORDER BY me.create_time DESC")
    IPage<MemoryEntry> findByUserIdAndCreateTimeBetween(@Param("userId") Long userId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime, Page<MemoryEntry> page);

    /**
     * 鏌ヨ鍖呭惈鏂囦欢鐨勬潯鐩垪琛?
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE file_url IS NOT NULL AND file_url != '' AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findEntriesWithFiles(Page<MemoryEntry> page);

    /**
     * 鏍规嵁鍐呭妯＄硦鏌ヨ鏉＄洰
     * @param content 鍐呭鍏抽敭瀛?
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE text_content LIKE CONCAT('%', #{content}, '%') AND is_deleted = 0 ORDER BY create_time DESC")
    IPage<MemoryEntry> findByContentLike(@Param("content") String content, Page<MemoryEntry> page);

    /**
     * 鍦ㄦ寚瀹氳兌鍥婁腑鏍规嵁鍐呭妯＄硦鏌ヨ鏉＄洰锛堝垎椤碉級
     * @param capsuleId 鑳跺泭ID
     * @param content 鍐呭鍏抽敭瀛?
     * @param page 鍒嗛〉鍙傛暟
     * @return 鏉＄洰鍒嗛〉鍒楄〃
     */
    @Select("SELECT * FROM capsule_entry WHERE capsule_id = #{capsuleId} AND text_content LIKE CONCAT('%', #{content}, '%') AND is_deleted = 0 ORDER BY sort_order ASC, create_time ASC")
    IPage<MemoryEntry> findByContentLikeInCapsule(@Param("capsuleId") Long capsuleId, @Param("content") String content, Page<MemoryEntry> page);

    /**
     * 鏍规嵁鐢ㄦ埛ID鏌ヨ鎵€鏈夎蹇嗘潯鐩?
     * @param userId 鐢ㄦ埛ID
     * @return 鏉＄洰鍒楄〃
     */
    @Select("SELECT me.* FROM capsule_entry me " +
            "INNER JOIN memory_capsule mc ON me.capsule_id = mc.id " +
            "WHERE mc.user_id = #{userId} AND me.is_deleted = 0 AND mc.is_deleted = 0 " +
            "ORDER BY me.create_time DESC")
    List<MemoryEntry> findByUserId(@Param("userId") Long userId);

    /**
     * 鏇存柊鏉＄洰鎺掑簭
     * @param entryId 鏉＄洰ID
     * @param sortOrder 鏂版帓搴忓€?
     * @return 褰卞搷琛屾暟
     */
    @Update("UPDATE capsule_entry SET sort_order = #{sortOrder}, update_time = NOW() WHERE id = #{entryId}")
    int updateSortOrder(@Param("entryId") Long entryId, @Param("sortOrder") Integer sortOrder);

    /**
     * 鎼滅储璁板繂鏉＄洰
     * @param userId 鐢ㄦ埛ID
     * @param keyword 鎼滅储鍏抽敭璇?
     * @param capsuleId 鑳跺泭ID锛堝彲閫夛級
     * @param page 鍒嗛〉鍙傛暟
     * @return 鎼滅储缁撴灉鍒嗛〉鍒楄〃
     */
    @Select("<script>" +
            "SELECT me.* FROM capsule_entry me " +
            "INNER JOIN memory_capsule mc ON me.capsule_id = mc.id " +
            "WHERE mc.user_id = #{userId} AND me.is_deleted = 0 AND mc.is_deleted = 0" +
            "<if test='keyword != null and keyword != \"\"\'> AND me.text_content LIKE CONCAT('%', #{keyword}, '%')</if>" +
            "<if test='capsuleId != null'> AND me.capsule_id = #{capsuleId}</if>" +
            " ORDER BY me.create_time DESC" +
            "</script>")
    IPage<MemoryEntry> searchEntries(@Param("userId") Long userId, @Param("keyword") String keyword, @Param("capsuleId") Long capsuleId, Page<MemoryEntry> page);

}
